# -*- coding: utf-8 -*-
# -----------------------------------------
# @Time       : 2018/6/18 10:04
# @Author     : dqz 
# @Email      : 1406990390@qq.com
# @File       : mysql.py
# @Software   : PyCharm
# -----------------------------------------
import pymysql

from settings import FILTER_DB


class Mysql:
    def __init__(self, conn):
        self.conn = conn
        self.db = None

    def connect(self, database):
        self.db = pymysql.connect(host=self.conn["host"],
                                  port=self.conn["port"],
                                  user=self.conn["username"],
                                  passwd=self.conn["password"],
                                  database=database,
                                  use_unicode=True,
                                  charset='utf8')
        return self

    def check_connect(self, database):
        try:
            self.db = pymysql.connect(host=self.conn["host"],
                                      port=self.conn["port"],
                                      user=self.conn["username"],
                                      passwd=self.conn["password"],
                                      database=database,
                                      use_unicode=True,
                                      charset='utf8')
            self.close()
            return True
        except Exception as e:
            self.db = None
            return False

    def check_table(self, database, table):
        sql = "show tables"
        self.connect(database)
        results = self.fetchall(sql)
        for result in results:
            if table in result.values():
                self.close()
                return True
        self.close()
        return False

    def commit(self):
        if self.db is not None:
            self.db.commit()

    def close(self):
        if self.db is not None:
            self.db.close()
            self.db = None

    def execute(self, sql):
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
        except Exception as e:
            self.db.rollback()
            print(e)
            return False
        return True

    def create_database(self, database):
        infos = {
            "status": False,
            "msg": ""
        }

        self.connect(None)

        sql = f"create database {database} DEFAULT CHARSET utf8 COLLATE utf8_general_ci"
        if self.execute(sql):
            infos["status"] = True
        else:
            infos["msg"] = f"create database: {database} failed!"
        return infos

    def fetchone_crude(self, sql):
        cursor = self.db.cursor()
        cursor.execute(sql)
        return cursor.fetchone()

    def fetchall_crude(self, sql):
        cursor = self.db.cursor()
        cursor.execute(sql)
        return cursor.fetchall()

    def fetchone(self, sql, return_json=False):
        cursor = self.db.cursor()
        infos = {
            "status": False,
            "data": [],
            "msg": ""
        }

        try:
            cursor.execute(sql)
            if return_json:
                columns = [col[0] for col in cursor.description]
                infos["data"] = dict(zip(columns, cursor.fetchone()))
            else:
                infos["data"] = cursor.fetchone()
            infos["status"] = True

        except Exception as e:
            infos["msg"] = str(e)

        return infos

    def fetchall(self, sql, return_json=False):
        cursor = self.db.cursor()
        infos = {
            "status": False,
            "data": [],
            "msg": ""
        }
        try:
            cursor.execute(sql)
            if return_json:
                columns = [col[0] for col in cursor.description]
                infos["data"] = [dict(zip(columns, row)) for row in cursor.fetchall()]
            else:
                infos["data"] = cursor.fetchall()
            infos["status"] = True
        except Exception as e:
            infos["msg"] = str(e)

        return infos

    def get_databases(self):
        self.connect(None)
        sql = "show databases"
        infos = {
            "status": False,
            "data": [],
            "msg": ""
        }
        database_info = self.fetchall(sql)

        filter_databases = FILTER_DB
        if database_info.get("status"):
            for row in database_info.get("data"):
                if row[0] in filter_databases:
                    continue
                infos["data"].append(row[0])
        else:
            infos["msg"] = database_info.get("msg")

        infos["status"] = database_info.get("status")
        return infos

    def get_tables(self, database):
        sql = "show tables"
        infos = {
            "status": False,
            "data": [],
            "msg": ""
        }
        self.connect(database)
        results = self.fetchall(sql)
        self.close()
        if results.get("status"):
            for result in results.get("data"):
                for key, value in result.items():
                    infos["data"].append(value)
        else:
            infos["msg"] = results.get("msg")
        infos["statis"] = result["status"]
        return infos

    def get_fields(self, database, table):
        sql = f"desc {table}"
        infos = {
            "status": False,
            "data": [],
            "msg": ""
        }
        if not self.check_table(database, table):
            infos["msg"] = f"database: {database} not found table: {table}"
            return infos
        fields_info = self.fetchall(sql, database)
        if fields_info.get("status"):
            for row in fields_info.get("data"):
                infos["data"].append(row[0])
        else:
            infos["msg"] = fields_info.get("msg")
        infos["status"] = fields_info.get("status")
        return infos
